﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using LumenWorks.Framework.IO.Csv;
using System.IO; 
using LumenWorks.Framework.Tests.Unit.IO.Csv;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Adex; 
using System.Transactions;
using System.Drawing;
using MediaMonitor.Common;

namespace MediaMonitor.Admin
{
    public partial class RF_Upload_Outdoor : Adex.BasePage
    {

        protected void Page_Load(object sender, EventArgs e)
        {
            //if (flAjaxUploader.IsPosting)
            //    this.ManagePost();

        }

        private void ManagePost()
        {
            //HttpPostedFileAJAX pf = flAjaxUploader.PostedFile;

            ////if (pf.ContentType.Equals("image/gif") && pf.ContentLength <= 5 * 1024)
            //    flAjaxUploader.SaveAs("~/temp", pf.FileName);
        }

        protected void btnUpload_Click(Object sender, EventArgs e)
        {

            string errMsg1, errMsg2, errMsg3, errMsg4, errMsg5, errMsg6, errMsg7;
            errMsg1 = errMsg2 = errMsg3 = errMsg4 = errMsg5 = errMsg6 = errMsg7 = "";


            if (flRF1.PostedFile.FileName.Length > 0)
            {
                lblInfo1.ForeColor = UploadRF(flRF1, out errMsg1) == true ? Color.Blue : Color.Red;
                lblInfo1.Text = errMsg1;
            }
            else
                lblInfo1.Text = "No file selected to upload";


            if (flRF2.PostedFile.FileName.Length > 0)
            {
                lblInfo2.ForeColor = UploadRF(flRF2, out errMsg2) == true ? Color.Blue : Color.Red;
                lblInfo2.Text = errMsg2;
            }
            else
                lblInfo2.Text = "No file selected to upload";

            if (flRF3.PostedFile.FileName.Length > 0)
            {
                lblInfo3.ForeColor = UploadRF(flRF3, out errMsg3) == true ? Color.Blue : Color.Red;
                lblInfo3.Text = errMsg3;
            }
            else
                lblInfo3.Text = "No file selected to upload";

            if (flRF4.PostedFile.FileName.Length > 0)
            {
                lblInfo4.ForeColor = UploadRF(flRF4, out errMsg4) == true ? Color.Blue : Color.Red;
                lblInfo4.Text = errMsg4;
            }
            else
                lblInfo4.Text = "No file selected to upload";

            if (flRF5.PostedFile.FileName.Length > 0)
            {
                lblInfo5.ForeColor = UploadRF(flRF5, out errMsg5) == true ? Color.Blue : Color.Red;
                lblInfo5.Text = errMsg5;
            }
            else
                lblInfo5.Text = "No file selected to upload";

            if (flRF6.PostedFile.FileName.Length > 0)
            {
                lblInfo6.ForeColor = UploadRF(flRF6, out errMsg6) == true ? Color.Blue : Color.Red;
                lblInfo6.Text = errMsg6;
            }
            else
                lblInfo6.Text = "No file selected to upload";

            if (flRF7.PostedFile.FileName.Length > 0)
            {
                lblInfo7.ForeColor = UploadRF(flRF7, out errMsg7) == true ? Color.Blue : Color.Red;
                lblInfo7.Text = errMsg7;
            }
            else
                lblInfo7.Text = "No file selected to upload";


            pnlInfo1.Visible = pnlInfo2.Visible = pnlInfo3.Visible = pnlInfo4.Visible = pnlInfo5.Visible = pnlInfo6.Visible = pnlInfo7.Visible = true;

            pnlUpload.Visible = false;


        }

        private bool UploadRF(FileUpload flRF, out string errMsg)
        {


            byte countryID = AppUser.GetCountry();

            StreamReader readFile = new StreamReader(flRF.PostedFile.InputStream);
            //FileInfo fl = new FileInfo(Server.MapPath("~/temp/" + pf.FileName));

            //StreamReader readFile = new StreamReader(fl.OpenRead());


            CsvReader csv = new CsvReader(new StringReader(readFile.ReadToEnd()), false, ',', '"', '\\', '☺', ValueTrimmingOptions.All); // for comments char = 122

            DataTable dt = GetRFDataTable();
            if (csv.FieldCount != dt.Columns.Count - 1)
            {
                errMsg = "<h2>Upload Failed!!!</h2>Not a valid RF file. Field length is different";
                return false;
            }


            int currentRow = 0;
            int currentCol = 0;

            try
            {
                //fill data from RF file
                while (csv.ReadNextRecord())
                {
                    currentRow += 1;

                    DataRow dr = dt.NewRow();
                    for (int i = 0; i < dt.Columns.Count - 1; i++)
                    {
                        currentCol = i + 1;

                        switch (dt.Columns[i + 1].DataType.Name)
                        {
                            case "DateTime":
                                dr[i + 1] = Utility.IsNullDate(Utility.GetDateFromNumber(csv[i].ToString()));
                                break;
                            case "Int32":
                                dr[i + 1] = Utility.IsNullInt(csv[i]);
                                break;
                            case "String":
                                dr[i + 1] = Utility.IsNullStr(csv[i]);
                                break;
                            case "Boolean":
                                dr[i + 1] = Utility.IsNullBool(csv[i]);
                                break;
                            case "Decimal":
                                dr[i + 1] = Utility.IsNullDec(csv[i]);
                                break;
                            case "Double":
                                dr[i + 1] = Utility.IsNullDec(csv[i]);
                                break;
                        }//end swtich


                    }
                    dt.Rows.Add(dr);
                }

            }
            catch (Exception ex1)
            {

                errMsg = "<h2>Upload Failed!!!</h2>Failed to shift data from CSV to DB. CSV Row: " + currentRow + ", Col: " + currentCol + "." + ex1.Message;
                return false;
            }


            TransactionOptions options = new TransactionOptions();
            options.Timeout = new TimeSpan(0, 2, 0);
            options.IsolationLevel = System.Transactions.IsolationLevel.ReadUncommitted;


            DateTime rfDate = DateTime.Now.AddYears(50);

            using (TransactionScope ts = new TransactionScope(TransactionScopeOption.Suppress, options))
            {
                using (SubSonic.SharedDbConnectionScope conn = new SubSonic.SharedDbConnectionScope(System.Configuration.ConfigurationManager.ConnectionStrings["DBAdex"].ToString()))
                {

                    //transaction activities.
                    try
                    {
                        //delete all the CommercialLog of same RFDate
                        try
                        {
                            rfDate = Convert.ToDateTime(dt.Rows[0]["RFDate"]);
                            RfController.DeleteAllOutDoorByDate(rfDate, countryID);
                        }
                        catch
                        {
                            ts.Dispose();
                            conn.Dispose();

                            errMsg = "<h2>Upload Failed!!!</h2>Failed to Delete previous Commercial records of same date.";
                            return false;
                        }


                        foreach (DataRow dr in dt.Rows)
                        {

                            rfDate = Utility.IsNullDate(dr["RFDate"], DateTime.MinValue);

                            int mediaCode = Utility.IsNullInt(dr["MediaCode"]);

                            int mediaTypeCode = MediaMonitor.Common.App.GetMediaTypeCode(mediaCode);

                            string mediaType = "";

                            switch (mediaTypeCode)
                            {
                                case 1:
                                case 3:
                                case 5:
                                    {
                                        mediaType = "TVCR";
                                        break;
                                    }
                                case 6:
                                    {
                                        mediaType = "PR";
                                        break;
                                    }
                                case 7:
                                    {
                                        mediaType = "OD";
                                        break;
                                    }
                                case 8:
                                    {
                                        mediaType = "NS";
                                        break;
                                    }
                            }


                            #region Advertiser

                            Advertiser adv = new Advertiser();
                            adv.MediaType = mediaType;
                            adv.AdvertiserCode = Utility.IsNullInt(dr["AdvertiserCode"]);
                            adv.AdvertiserName = Utility.IsNullStr(dr["AdvertiserName"], "");

                            #endregion

                            #region Agency

                            Agency agnc = new Agency();
                            agnc.MediaType = mediaType;
                            agnc.AgencyCode = Utility.IsNullInt(dr["AgencyCode"]);
                            agnc.AgencyName = Utility.IsNullStr(dr["AgencyName"], "");

                            #endregion

                            #region Sector

                            Sector sec = new Sector();
                            sec.MediaType = mediaType;
                            sec.SectorCode = Utility.IsNullInt(dr["SectorCode"]);
                            sec.SectorName = Utility.IsNullStr(dr["SectorName"], "");


                            #endregion

                            #region SubSector

                            SubSector subSec = new SubSector();
                            subSec.MediaType = mediaType;
                            subSec.SubSectorCode = Utility.IsNullInt(dr["SubSectorCode"]);
                            subSec.SubSectorName = Utility.IsNullStr(dr["SubSectorName"], "");
                            subSec.SectorID = sec.SectorID;

                            #endregion

                            #region Category

                            Category cat = new Category();
                            cat.MediaType = mediaType;
                            cat.CategoryCode = Utility.IsNullInt(dr["CategoryCode"]);
                            cat.CategoryName = Utility.IsNullStr(dr["CategoryName"], "");
                            cat.SubSectorID = subSec.SubSectorID;

                            #endregion

                            #region Brand

                            Brand brnd = new Brand();
                            brnd.MediaType = mediaType;
                            brnd.BrandCode = Utility.IsNullInt(dr["BrandCode"]);
                            brnd.BrandName = Utility.IsNullStr(dr["BrandName"], "");
                            //brnd.AdvertiserCode = adv.AdvertiserCode;


                            #endregion

                            #region Update Advertiser / Agency / Sector / Sub Sector / Category / SubSector 2 / Brand /
                            switch (mediaTypeCode)
                            {
                                case 1:
                                    {
                                        adv.Tv = true;
                                        agnc.Tv = true;
                                        sec.Tv = true;
                                        subSec.Tv = true;
                                        cat.Tv = true;
                                        brnd.Tv = true;

                                        break;
                                    }
                                case 3:
                                    {
                                        adv.Rd = true;
                                        agnc.Rd = true;
                                        sec.Rd = true;
                                        subSec.Rd = true;
                                        cat.Rd = true;
                                        brnd.Rd = true;
                                        break;
                                    }
                                case 5:
                                    {
                                        adv.Cb = true;
                                        agnc.Cb = true;
                                        sec.Cb = true;
                                        subSec.Cb = true;
                                        cat.Cb = true;
                                        brnd.Cb = true;
                                        break;
                                    }
                                case 6:
                                    {
                                        adv.Pr = true;
                                        agnc.Pr = true;
                                        sec.Pr = true;
                                        subSec.Pr = true;
                                        cat.Pr = true;
                                        brnd.Pr = true;
                                        break;
                                    }
                                case 7:
                                    {
                                        adv.Od = true;
                                        agnc.Od = true;
                                        sec.Od = true;
                                        subSec.Od = true;
                                        cat.Od = true;
                                        brnd.Od = true;
                                        break;
                                    }
                                case 8:
                                    {
                                        adv.Ns = true;
                                        agnc.Ns = true;
                                        sec.Ns = true;
                                        subSec.Ns = true;
                                        cat.Ns = true;
                                        brnd.Ns = true;
                                        break;
                                    }
                            }//end switch


                            RfController.AddOrUpdateAdvertiser(ref adv);

                            RfController.AddOrUpdateAgency(ref agnc);
                            RfController.AddOrUpdateSector(ref sec);

                            subSec.SectorID = sec.SectorID;
                            RfController.AddOrUpdateSubSector(ref subSec);

                            cat.SubSectorID = subSec.SubSectorID;
                            RfController.AddOrUpdateCategory(ref cat);

                            RfController.AddOrUpdateBrand(ref brnd);



                            #endregion

                            #region Prod

                            //Product prod = new Product("ProductCode", Utility.IsNullInt(dr["ProductCode"]));
                            //if (prod.ProductID == 0)
                            //{
                                //prod doesn't already exists.
                            //prod = new Product();
                                Product prod = new Product();    
                                prod.MediaType = mediaType;
                                prod.BrandID = brnd.BrandID;
                                prod.ProductCode = Utility.IsNullInt(dr["ProductCode"]);
                                prod.ProductName = Utility.IsNullStr(dr["ProdVersionName"]);
                                prod.DateAdded = DateTime.Now;

                                RfController.AddOrUpdateProduct(ref prod, mediaTypeCode, rfDate);
                           // }

                            #endregion

                            #region ProdVersion

                            ProdVersion ver = new ProdVersion();
                            ver.ProdVersionCode = Utility.IsNullInt(dr["ProdVersionCode"]);
                            ver.ProdVersionName = Utility.IsNullStr(dr["ProdVersionName"]);
                            ver.BrandID = brnd.BrandID;
                            if (prod.ProductID > 0)
                                ver.ProductID = prod.ProductID;

                            ver.ShortVideo = true;
                            ver.FullVideo = true;
                            //ver.ShortVideoFile = ver.ProdVersionCode.ToString() + "_s.wmv";
                            ver.ShortVideoFile = ver.ProdVersionCode.ToString() + ".wmv";
                            ver.FullVideoFile = ver.ProdVersionCode.ToString() + ".wmv";

                            ver.RadioFile = ver.ProdVersionCode.ToString() + ".wma";
                            ver.CableFile = ver.ProdVersionCode.ToString() + ".wma";

                            RfController.AddOrUpdateProdVersion(ref ver, mediaTypeCode, rfDate);

                            #endregion

                            #region Visibility

                            Visibility vb = new Visibility();
                            vb.VisibilityCode = Utility.IsNullInt(dr["VisibilityCode"]);
                            vb.VisibilityName = Utility.IsNullStr(dr["VisibilityName"]);

                            RfController.AddOrUpdateVisibility(vb);
                            #endregion

                            #region Carrier

                            Carrier cr = new Carrier();
                            cr.CarrierCode = Utility.IsNullInt(dr["CarrierCode"]);
                            cr.CarrierName = Utility.IsNullStr(dr["CarrierName"]);


                            RfController.AddOrUpdateCarrier(cr);

                            #endregion

                            #region  OutDoorLog

                            Adex.OutdoorLog odLog = new OutdoorLog();

                            odLog.CountryID = countryID;

                            odLog.RFDate = rfDate;
                            odLog.MediaCode = mediaCode;

                            odLog.TimeStart = Convert.ToInt32(dr["TimeStart"]);
                            odLog.TimeEnd = Convert.ToInt32(dr["TimeEnd"]);

                            odLog.TimeStartFormatted = Utility.GetTimeFromNumbers(Utility.IsNullStr(dr["TimeStart"].ToString(), "000000"));
                            odLog.TimeEndFormatted = Utility.GetTimeFromNumbers(Utility.IsNullStr(dr["TimeEnd"].ToString(), "000000"));

                            odLog.Size = Utility.ToInt(dr["Size"]);

                            odLog.Quality = Utility.IsNullStr(dr["Quality"]);

                            odLog.RealCost = Utility.IsNullDec(dr["RealCost"]);

                            odLog.LatLong = Utility.IsNullStr(dr["LatLong"]);

                            odLog.NoOfAds = Utility.IsNullInt(dr["NoOfAds"]);

                            odLog.TotalAdsInThatLocation = Utility.ToInt(dr["TotalAdsInThatLocation"]);

                            odLog.TypeOfAds = Utility.IsNullInt(dr["TypeOfAds"]);



                            odLog.BetweenAvenue = Utility.IsNullStr(dr["BetweenAvenue"]);
                            odLog.PhotoCode = Utility.IsNullInt(dr["PhotoCode"]);

                            odLog.VerificationDate = Utility.IsNullDate(dr["VerificationDate"]);
                            odLog.VerificationDesc = Utility.IsNullStr(dr["VerificationDesc"]);
                            //foriegn keys 

                            odLog.CarrierCode = cr.CarrierCode;
                            odLog.ProductID = Utility.ToInt(prod.ProductID);
                            odLog.ProdVersionID = ver.ProdVersionID;
                            odLog.CategoryID = cat.CategoryID;
                            odLog.SectorID = sec.SectorID;
                            odLog.SubSectorID = subSec.SubSectorID;
                            odLog.BrandID = brnd.BrandID;
                            odLog.AdvertiserID = adv.AdvertiserID;
                            odLog.AgencyID = agnc.AgencyID;
                            odLog.VisibilityCode = Utility.IsNullInt(dr["VisibilityCode"]);


                            odLog.DateAdded = DateTime.Now;
                            odLog.DateModified = DateTime.Now;

                            odLog.Save();
                            // RfController.AddOrUpdateCommercialLog(comLog);


                            #endregion

                        } //end loop dt

                        ts.Complete();
                    }//end try

                    catch (Exception ex2)
                    {
                        ts.Dispose();

                        conn.Dispose();

                        try
                        {
                            RfController.DeleteAllOutDoorByDate(rfDate, AppUser.GetCountry());
                        }
                        catch { }

                        errMsg = "<h2>System fails to upload data process</h2>No data uploaded, Please try again.<br>" + ex2.Message;
                        return false;

                    }

                }//end using SharedDBConnection
            }//end using TransactionScope

            errMsg = "<h2>Data Uploaded Successfully</h2>All data from RF file successfully uploaded";
            return true;

        }

        private System.Data.DataTable GetRFDataTable()
        {
            DataTable dt = new DataTable();
            //dt = Adex.Rf.Query().WHERE("ID", 0).ExecuteDataSet().Tables[0];
            dt.Columns.Add("ID", System.Type.GetType("System.Int32"));
            dt.Columns.Add("MediaCode", System.Type.GetType("System.Int32")); // Avenue is MediaCode
            dt.Columns.Add("RFDate", System.Type.GetType("System.DateTime"));
            dt.Columns.Add("TimeStart", System.Type.GetType("System.Int32"));
            dt.Columns.Add("TimeEnd", System.Type.GetType("System.Int32"));
            dt.Columns.Add("Size", System.Type.GetType("System.Int32"));
            dt.Columns.Add("CarrierCode", System.Type.GetType("System.Int32"));
            dt.Columns.Add("CarrierName", System.Type.GetType("System.String"));
            dt.Columns.Add("Quality", System.Type.GetType("System.String"));
            dt.Columns.Add("RealCost", System.Type.GetType("System.Decimal"));
            dt.Columns.Add("LatLong", System.Type.GetType("System.String"));
            dt.Columns.Add("NoOfAds", System.Type.GetType("System.Int32"));
            dt.Columns.Add("TotalAdsInThatLocation", System.Type.GetType("System.Int32"));
            dt.Columns.Add("TypeOfAds", System.Type.GetType("System.Int32"));
            dt.Columns.Add("VisibilityCode", System.Type.GetType("System.Int32"));
            dt.Columns.Add("VisibilityName", System.Type.GetType("System.String"));
            dt.Columns.Add("VerificationDate", System.Type.GetType("System.DateTime"));
            dt.Columns.Add("VerificationDesc", System.Type.GetType("System.String"));
            dt.Columns.Add("ProdVersionCode", System.Type.GetType("System.Int32"));
            dt.Columns.Add("ProdVersionName", System.Type.GetType("System.String")); //19

            dt.Columns.Add("ProductCode", System.Type.GetType("System.Int32")); //product name will be taken from ProdVersionName

            dt.Columns.Add("Constant4", System.Type.GetType("System.String"));

            dt.Columns.Add("CategoryCode", System.Type.GetType("System.Int32"));
            dt.Columns.Add("CategoryName", System.Type.GetType("System.String"));
            dt.Columns.Add("SubSectorCode", System.Type.GetType("System.Int32"));
            dt.Columns.Add("SubSectorName", System.Type.GetType("System.String"));
            dt.Columns.Add("SectorCode", System.Type.GetType("System.Int32"));
            dt.Columns.Add("SectorName", System.Type.GetType("System.String"));
            dt.Columns.Add("BetweenAvenue", System.Type.GetType("System.String"));

            dt.Columns.Add("BrandCode", System.Type.GetType("System.Int32"));
            dt.Columns.Add("BrandName", System.Type.GetType("System.String"));
            dt.Columns.Add("AdvertiserCode", System.Type.GetType("System.Int32"));
            dt.Columns.Add("AdvertiserName", System.Type.GetType("System.String"));
            dt.Columns.Add("PhotoCode", System.Type.GetType("System.Int32"));
            dt.Columns.Add("AgencyCode", System.Type.GetType("System.Int32"));
            dt.Columns.Add("AgencyName", System.Type.GetType("System.String"));

            return dt;

        }


    }
}